1. Technical Field
The invention relates to relational database systems and more particularly to application of Structured Query Language (SQL) to user collected raw data stored in formats other than that for a base relation for a given relational database management system (DBMS). Such user collected raw data includes data objects such as spreadsheets, statistical industrial application data or a data stream on an input/output port. Still more particularly, the invention relates to providing user defined data management service functions, relation definitions and query language extensions to support application of SQL to such data.
2. Glossary
The following definitions are used:
Application or User files. A named set of records stored and processed as unit and having a structure determined by a computer application program.
Atomic data. Representations to which meaning may be assigned, which are physically recorded and which cannot be decomposed into smaller units by a database manager.
Compound Data. Structured combinations of atomic data which can be decomposed by a database manager.
Database. A collection of data with a given structure for accepting, storing, and providing, on demand, data for multiple users.
Data management functions. A set of functions used by a database manager to manipulate a base relation and extended by the invention to application files.
Information. The agreed meaning of data.
Relation. An arrangement of atomic data into columns and unordered rows. Rows should not be duplicated.
Relational database. A database in which all compound data objects are organized as columns and unordered rows.
Structured Query Language (SQL). A set of facilities for defining, accessing and managing a relational database. It is processed linearly.
Table. Commonly used as a synonym for relation, but here used to refer to the user model of base relations and various derived relations, such as views or result tables.
Transaction. A collection of activities involving changes to a database.
Tuple. A row of atomic data instances in a relation. A tuple may correspond to a record or segment in a nonrelational database structure.
View. A virtual or derived relation which, prior to the present invention, was defined in terms of base relations, and represented by its defining expression. A view is here defined in terms of base relations and other files for which a user defined table has been provided.
Virtual relation. Synonymous with derived relation. The result of operations (i.e., a defining expression) carried out on one or more base relations and application files but not itself constituting compound data. It is represented to a user as a table.
User defined table. The user model of an application file and definitional data structures comprising a set of user defined data management functions for a user defined table type and column descriptions for the application file.
User defined table type. A category for one or more application files defined by a set of use defined data management functions.
3. Description of the Prior Art
Relational database management systems were traditionally applied in fields such as insurance, banking and personnel. Their extension to areas such as engineering databases has been hampered by the lack of capacity of venders of relational database products to provide increasingly complex data types and user defined functions.
SQL is, as of 1993, the most widespread user interface language used with relational database management systems. Its application to user collected raw data files, such as .spread sheets, has however been constrained by the need to conform such files to a relation definition cognizable to specific relational database products. Import/export tools have been used to generate conforming files or base relations from application files for insertion to a database.
Import/export tools are typically application specific programs provided by vendors, or written by database users. Vendor supplied and supported import/export tools are comparatively rare, being available only for the most popular application programs. For a user to write an import/export tool requires considerable knowledge of the database manager for the particular database system in use.
Even where an import/export tool is available, it is not a cost free solution in terms of data processing efficiency. The import/export tool does 8 not eliminate existence of the application file, which may in fact still be needed. Operation of the import/export tool results in the need to store files of differing format which contain duplicate data. The larger the application file the larger the problem potentially caused by the need to duplicate its data. Import/export tools have also required offline operation and thus cannot be applied to real time communication applications.